USE master
go

--DROP DATABASE CollegeWebsite

CREATE DATABASE CollegeWebsite
GO

USE CollegeWebsite
GO


CREATE TABLE [dbo].[Admins] (
[AdminID] int NOT NULL IDENTITY(1,1) ,
[UserName] varchar(50) NOT NULL ,
[PassWord] varchar(32) NOT NULL ,
[AdminName] nvarchar(100) NOT NULL ,
[Status] bit NOT NULL DEFAULT ((1)) 
)


-- ----------------------------
-- Table structure for [dbo].[Admissions]
-- ----------------------------
CREATE TABLE [dbo].[Admissions] (
[CourseID] int NULL ,
[StudentID] int NULL ,
[Status] bit NOT NULL DEFAULT ((0)) 
)


GO


-- ----------------------------
-- Table structure for [dbo].[Contacts]
-- ----------------------------
CREATE TABLE [dbo].[Contacts] (
[ContactID] int NOT NULL IDENTITY(1,1) ,
[ContactName] nvarchar(250) NOT NULL ,
[ContactAddress] nvarchar(500) NULL ,
[ContactPhone] nvarchar(20) NULL ,
[ContactEmail] nvarchar(100) NULL ,
[ContactFax] nvarchar(20) NULL ,
[ContactStatus] bit NULL ,
[DepartmentID] int NULL 
)


-- ----------------------------
-- Table structure for [dbo].[Courses]
-- ----------------------------
CREATE TABLE [dbo].[Courses] (
[CourseID] int NOT NULL IDENTITY(1,1) ,
[FacultyID] int NULL ,
[CourseName] nvarchar(50) NULL ,
[CourseCapicity] int NULL ,
[CourseFee] float(53) NULL ,
[Status] bit NOT NULL DEFAULT ((1)) 
)


-- ----------------------------
-- Table structure for [dbo].[Departments]
-- ----------------------------
CREATE TABLE [dbo].[Departments] (
[DepartmentID] int NOT NULL IDENTITY(1,1) ,
[DepartmentName] nvarchar(50) NULL ,
[DepartmentDescription] nvarchar(MAX) NULL ,
[Status] bit NOT NULL DEFAULT ((1)) 
)


-- ----------------------------
-- Table structure for [dbo].[Facilities]
-- ----------------------------
CREATE TABLE [dbo].[Facilities] (
[FacilityID] int NOT NULL IDENTITY(1,1) ,
[FacilityName] nvarchar(100) NULL ,
[FacilityDescription] nvarchar(MAX) NULL ,
[FacilityCapicity] int NULL ,
[FacilityFee] float(53) NULL 
)


-- ----------------------------
-- Table structure for [dbo].[Faculties]
-- ----------------------------
CREATE TABLE [dbo].[Faculties] (
[FacultyID] int NOT NULL IDENTITY(1,1) ,
[FacultyName] nvarchar(50) NOT NULL ,
[FacultyDesciption] nvarchar(MAX) NULL ,
[DepartmentID] int NOT NULL ,
[Status] bit NOT NULL DEFAULT ((0)) 
)


-- ----------------------------
-- Table structure for [dbo].[FeedBacks]
-- ----------------------------
CREATE TABLE [dbo].[FeedBacks] (
[FeedBackId] int NOT NULL IDENTITY(1,1) ,
[FromName] nvarchar(50) NOT NULL ,
[FromEmail] nvarchar(100) NOT NULL ,
[FeedSubject] nvarchar(200) NOT NULL ,
[FeedContent] nvarchar(500) NOT NULL ,
[FeedBackRespond] nvarchar(500) NULL ,
[FeedBackStatus] bit NOT NULL DEFAULT ((0)) ,
[FeedBackDate] datetime NOT NULL DEFAULT (getdate()) 
)


-- ----------------------------
-- Table structure for [dbo].[Menus]
-- ----------------------------
CREATE TABLE [dbo].[Menus] (
[MenuID] int NOT NULL IDENTITY(1,1) ,
[ParentID] int NOT NULL DEFAULT ((0)) ,
[Level] int NULL DEFAULT ((0)) ,
[MenuName] nvarchar(50) NOT NULL ,
[TextContent] nvarchar(100) NOT NULL ,
[Link] nvarchar(200) NULL ,
[Icons] nvarchar(200) NULL ,
[Publish] bit NOT NULL DEFAULT ((0)) 
)
GO

-- ----------------------------
-- Table structure for [dbo].[StudentFacility]
-- ----------------------------
CREATE TABLE [dbo].[StudentFacility] (
[FacilityID] int NOT NULL ,
[StudentID] int NOT NULL 
)
GO


-- ----------------------------
-- Table structure for [dbo].[StudentPreviousInfomations]
-- ----------------------------
CREATE TABLE [dbo].[StudentPreviousInfomations] (
[PreviousInfomationID] int NOT NULL IDENTITY(1,1) ,
[StudentID] int NOT NULL ,
[University] nvarchar(250) NULL ,
[EnrollmentNo] int NULL ,
[Center] nchar(10) NULL ,
[Stream] nchar(10) NULL ,
[Field] nchar(10) NULL ,
[MarksSecured] nchar(10) NULL ,
[OutOfDate] datetime NULL ,
[ClassObitained] nchar(10) NULL ,
[Status] bit NOT NULL 
)
GO

-- ----------------------------
-- Table structure for [dbo].[Students]
-- ----------------------------
CREATE TABLE [dbo].[Students] (
[StudentID] int NOT NULL IDENTITY(1,1) ,
[StudentName] nvarchar(50) NULL ,
[FatherName] nvarchar(50) NULL ,
[MotherName] nvarchar(50) NULL ,
[PIN] varchar(20) NOT NULL ,
[DateOfBirth] datetime NULL ,
[Gender] bit NOT NULL DEFAULT ((0)) ,
[ResidentialAddress] nvarchar(200) NULL ,
[PermanentAddress] nvarchar(200) NULL ,
[SupportDetails] nvarchar(500) NULL ,
[Status] bit NOT NULL DEFAULT ((1)) 
)
GO
-- ----------------------------
-- Table structure for [dbo].[StudentSubject]
-- ----------------------------
CREATE TABLE [dbo].[StudentSubject] (
[StudentSubjectID] int NOT NULL IDENTITY(1,1) ,
[StudentID] int NULL ,
[SubjectID] int NOT NULL 
)
GO
-- ----------------------------
-- Table structure for [dbo].[Subjects]
-- ----------------------------
CREATE TABLE [dbo].[Subjects] (
[SubjectID] int NOT NULL IDENTITY(1,1) ,
[SubjectName] nvarchar(150) NULL ,
[SujectDescription] nvarchar(250) NULL ,
[CourseID] varchar(150) NULL ,
[Status] bit NULL 
)
GO
-- ----------------------------
-- Primary Key structure for table [dbo].[Admins]
-- ----------------------------
ALTER TABLE [dbo].[Admins] ADD PRIMARY KEY ([AdminID])
GO

-- ----------------------------
-- Uniques structure for table [dbo].[Admins]
-- ----------------------------
ALTER TABLE [dbo].[Admins] ADD UNIQUE ([UserName] ASC)
GO

-- ----------------------------
-- Indexes structure for table Contacts
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[Contacts]
-- ----------------------------
ALTER TABLE [dbo].[Contacts] ADD PRIMARY KEY ([ContactID])
GO

-- ----------------------------
-- Indexes structure for table Courses
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[Courses]
-- ----------------------------
ALTER TABLE [dbo].[Courses] ADD PRIMARY KEY ([CourseID])
GO

-- ----------------------------
-- Indexes structure for table Departments
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[Departments]
-- ----------------------------
ALTER TABLE [dbo].[Departments] ADD PRIMARY KEY ([DepartmentID])
GO

-- ----------------------------
-- Indexes structure for table Facilities
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[Facilities]
-- ----------------------------
ALTER TABLE [dbo].[Facilities] ADD PRIMARY KEY ([FacilityID])
GO

-- ----------------------------
-- Indexes structure for table Faculties
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[Faculties]
-- ----------------------------
ALTER TABLE [dbo].[Faculties] ADD PRIMARY KEY ([FacultyID])
GO

-- ----------------------------
-- Indexes structure for table FeedBacks
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[FeedBacks]
-- ----------------------------
ALTER TABLE [dbo].[FeedBacks] ADD PRIMARY KEY ([FeedBackId])
GO

-- ----------------------------
-- Indexes structure for table Menus
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[Menus]
-- ----------------------------
ALTER TABLE [dbo].[Menus] ADD PRIMARY KEY ([MenuID])
GO

-- ----------------------------
-- Indexes structure for table StudentPreviousInfomations
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[StudentPreviousInfomations]
-- ----------------------------
ALTER TABLE [dbo].[StudentPreviousInfomations] ADD PRIMARY KEY ([PreviousInfomationID])
GO

-- ----------------------------
-- Indexes structure for table Students
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[Students]
-- ----------------------------
ALTER TABLE [dbo].[Students] ADD PRIMARY KEY ([StudentID])
GO

-- ----------------------------
-- Uniques structure for table [dbo].[Students]
-- ----------------------------
ALTER TABLE [dbo].[Students] ADD UNIQUE ([PIN] ASC)
GO

-- ----------------------------
-- Indexes structure for table StudentSubject
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[StudentSubject]
-- ----------------------------
ALTER TABLE [dbo].[StudentSubject] ADD PRIMARY KEY ([StudentSubjectID])
GO

-- ----------------------------
-- Indexes structure for table Subjects
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[Subjects]
-- ----------------------------
ALTER TABLE [dbo].[Subjects] ADD PRIMARY KEY ([SubjectID])
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[Admissions]
-- ----------------------------
ALTER TABLE [dbo].[Admissions] ADD FOREIGN KEY ([CourseID]) REFERENCES [dbo].[Courses] ([CourseID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[Admissions] ADD FOREIGN KEY ([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[Courses]
-- ----------------------------
ALTER TABLE [dbo].[Courses] ADD FOREIGN KEY ([FacultyID]) REFERENCES [dbo].[Faculties] ([FacultyID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[Faculties]
-- ----------------------------
ALTER TABLE [dbo].[Faculties] ADD FOREIGN KEY ([DepartmentID]) REFERENCES [dbo].[Departments] ([DepartmentID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[StudentFacility]
-- ----------------------------
ALTER TABLE [dbo].[StudentFacility] ADD FOREIGN KEY ([FacilityID]) REFERENCES [dbo].[Facilities] ([FacilityID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[StudentFacility] ADD FOREIGN KEY ([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[StudentPreviousInfomations]
-- ----------------------------
ALTER TABLE [dbo].[StudentPreviousInfomations] ADD FOREIGN KEY ([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[StudentSubject]
-- ----------------------------
ALTER TABLE [dbo].[StudentSubject] ADD FOREIGN KEY ([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[StudentSubject] ADD FOREIGN KEY ([SubjectID]) REFERENCES [dbo].[Subjects] ([SubjectID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
